package com.dy.yunying.biz.dao.znfx;

import com.alibaba.fastjson.JSON;
import com.dy.yunying.api.entity.znfx.ChartDataDetail;
import com.dy.yunying.api.req.znfx.ZnfxReq;
import com.dy.yunying.biz.config.YunYingProperties;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

/**
 * @description:
 * @author: zhuxm
 * @time: 2023/3/21 15:25
 */
@Slf4j
@Component
public class PaidUserDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	/**
	 * 查询点击时间分布
	 * @param znfxReq
	 * @return
	 */
	public List<ChartDataDetail> selectClicktimeData(ZnfxReq znfxReq) {
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
						  "		toHour(toDateTime(click_time/1000)) as name, \n" +
				          "		count(1) as value \n" +
				          "from " + yunYingProperties.getNinetydeviceregtable()).append(" reg \n");
		collectSql.append("where  1=1 \n");
		collectSql.append(this.getRegParentchlFilterSql(znfxReq));
		collectSql.append(this.getRegAmountFilterSql(znfxReq));
		collectSql.append("and reg.click_time !=0 \n");
		collectSql.append("and reg.kid in \n");
		collectSql.append("( \n");
		collectSql.append("		select \n" +
				"					d_kid \n" +
				"				from " + yunYingProperties.getDeviceregisterRechargetable() + " recharge \n" +
				"    			where 1=1 \n");
		collectSql.append(this.getRechargeParentchlFilterSql(znfxReq));
		collectSql.append(this.getPaydayFilterSql(znfxReq));
		collectSql.append(") \n");
		collectSql.append("group by toHour(toDateTime(click_time/1000)) \n");
		collectSql.append("order by toHour(toDateTime(click_time/1000)) asc\n");
		collectSql.append("WITH FILL \n" +
				"FROM 0 TO 24 \n" +
				"STEP 1 " );
		log.info("查询点击时间分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();
		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询点击时间分布耗时: {}ms", end - start);
		return  list;
	}



	/**
	 * 查询地域分布
	 * @param znfxReq
	 * @return
	 */
	public List<ChartDataDetail> selectAreaData(ZnfxReq znfxReq) {
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				          " 	IF(vip.dx_province = '' or vip.dx_province is null, IF(vip.dx_country ='' or vip.dx_country is null, '未知', vip.dx_country), vip.dx_province) as name, \n" +
				          "     count(1) as value \n" +
				          " from " + yunYingProperties.getNinetydeviceregtable() + " reg \n ");
		collectSql.append(" left join " + yunYingProperties.getIpTable() + " vip \n");
		collectSql.append(" on replace(arrayStringConcat(arrayPopBack(splitByString('.', splitByString(',', reg.ip)[1])), '.'), ' ', '' ) = vip.dx_short_ip \n");
		collectSql.append(" where  1=1 \n");
		collectSql.append(this.getRegParentchlFilterSql(znfxReq));
		collectSql.append(this.getRegAmountFilterSql(znfxReq));
		collectSql.append(" and reg.kid in \n");
		collectSql.append(" ( \n");
		collectSql.append("    select \n" +
				"			   		d_kid \n" +
				"				from " + yunYingProperties.getDeviceregisterRechargetable() + " recharge \n" +
				"    			where 1=1 \n");
		collectSql.append(this.getRechargeParentchlFilterSql(znfxReq));
		collectSql.append(this.getPaydayFilterSql(znfxReq));
		collectSql.append(" ) \n");
		collectSql.append("group by name \n");
		collectSql.append("order by name asc");

		log.info("查询地域分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();
		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询地域分布耗时: {}ms", end - start);
		return  list;
	}

	public List<ChartDataDetail> selectOsData(ZnfxReq znfxReq) {
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"    	   		CASE WHEN reg.os = 0 THEN concat('Android', reg.os_version) \n" +
				"        			 WHEN reg.os = 1 THEN concat('iPhone', reg.os_version) \n" +
				"        			 WHEN reg.os = 3 THEN '其他' \n" +
				"        			 WHEN reg.os = 4 THEN reg.os_version\n" +
				"        		     WHEN reg.os = 5 THEN reg.os_version \n" +
				"        			 ELSE reg.os_version END as name,\n" +
				"        		count(1) as value \n" +
				"          from " + yunYingProperties.getNinetydeviceregtable() + " reg \n");
		collectSql.append("where  1=1 \n");
		collectSql.append(this.getRegParentchlFilterSql(znfxReq));
		collectSql.append(this.getRegAmountFilterSql(znfxReq));
		collectSql.append(" and reg.kid in \n");
		collectSql.append(" ( \n");
		collectSql.append(" 	select \n" +
				"					d_kid \n" +
				"				from " + yunYingProperties.getDeviceregisterRechargetable() + " recharge \n" +
				"    			where 1=1 ");
		collectSql.append(this.getRechargeParentchlFilterSql(znfxReq));
		collectSql.append(this.getPaydayFilterSql(znfxReq));
		collectSql.append(") \n");
		collectSql.append("group by name \n");
		collectSql.append("order by name asc");
		log.info("查询系统分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();
		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询系统分布耗时: {}ms", end - start);
		return  list;
	}

	public List<ChartDataDetail> selectModeData(ZnfxReq znfxReq) {
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"    	   		reg.device_brand as name, \n" +
				"				count(1) as value \n" +
				"			from " + yunYingProperties.getNinetydeviceregtable() + " reg \n");
		collectSql.append("where  1=1 \n");
		collectSql.append(this.getRegParentchlFilterSql(znfxReq));
		collectSql.append(this.getRegAmountFilterSql(znfxReq));
		collectSql.append(" and reg.kid in \n");
		collectSql.append(" ( \n");
		collectSql.append(" 	select \n" +
				"					d_kid \n" +
				"				from " + yunYingProperties.getDeviceregisterRechargetable() + " recharge \n" +
				"    			where 1=1 \n");
		collectSql.append(this.getRechargeParentchlFilterSql(znfxReq));
		collectSql.append(this.getPaydayFilterSql(znfxReq));
		collectSql.append(" ) \n");
		collectSql.append("and reg.device_brand !='' and reg.device_brand is not null \n");
		collectSql.append("group by reg.device_brand \n");
		collectSql.append("order by reg.device_brand asc");
		log.info("查询机型分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();
		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询机型分布耗时: {}ms", end - start);
		return  list;
	}

	public List<ChartDataDetail> selectMedioData(ZnfxReq znfxReq) {
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"  		   		concat(concat(toString(ctype), '-'), toString(csite)) as name, \n" +
				"				count(1) as value \n" +
				"		   from " + yunYingProperties.getNinetydeviceregtable() + " reg \n" +
				"		   where  1=1 \n");
		collectSql.append(this.getRegParentchlFilterSql(znfxReq));
		collectSql.append(this.getRegAmountFilterSql(znfxReq));
		collectSql.append(" and reg.kid in \n");
		collectSql.append(" ( \n");
		collectSql.append("    	select \n" +
				"					d_kid \n" +
				"				from " + yunYingProperties.getDeviceregisterRechargetable() + " recharge \n" +
				"    			where 1=1 \n");
		collectSql.append(this.getRechargeParentchlFilterSql(znfxReq));
		collectSql.append(this.getPaydayFilterSql(znfxReq));
		collectSql.append(" ) \n ");
		collectSql.append(" and reg.csite !='' and reg.csite is not null \n");
		collectSql.append(" group by ctype, csite \n");
		collectSql.append(" order by ctype asc, csite asc");
		log.info("查询媒体来源SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();
		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询媒体来源耗时: {}ms", end - start);
		return  list;
	}

	public List<ChartDataDetail> selectAgeData(ZnfxReq znfxReq) {
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"    	   		IF(tmp_age=-1, '未实名', toString(tmp_age)) as name, \n" +
				"				count(distinct username) as value \n" +
				"		   from ( \n" +
				"      	   		select \n" +
				"					recharge.username,\n" +
				"					IF(LENGTH(realname.id_num) = 18, CAST(SUBSTRING(realname.id_num, 7, 4) as Int), toYear(today())+1) as brith_year,\n" +
				"            		toYear(today()) - brith_year as age, \n" +
				"            		IF(age=-1, -1, (case when age<18 then 17 \n" +
				"            			when age>=18 and age<24 then 18\n" +
				"            			when age>=24 and age<31 then 24\n" +
				"            			when age>=31 and age<41 then 31\n" +
				"            			when age>=41 and age<50 then 41\n" +
				"            			else 50 end)) AS tmp_age \n" +
				" 				from " + yunYingProperties.getDeviceregisterRechargetable() + "  recharge \n" +
				" 				left join " + yunYingProperties.getWanUserRealnameView() + " realname \n" +
				" 				on toUInt64(recharge.game_main) = realname.pgid \n" +
				" 				and realname.ai = recharge.username \n" +
				" 				and realname.status =0 \n" +
				" 				where  1=1 \n");
		collectSql.append(this.getRechargeParentchlFilterSql(znfxReq));
		collectSql.append(this.getPaydayFilterSql(znfxReq));
		collectSql.append(
				" 				and recharge.d_kid in \n");
		collectSql.append(" 	( \n");
		collectSql.append("    		select \n" +
				"						kid \n" +
				"					from " + yunYingProperties.getNinetydeviceregtable() + " reg \n" +
				"    				where 1=1 \n");
		collectSql.append(this.getRegParentchlFilterSql(znfxReq));
		collectSql.append(this.getRegAmountFilterSql(znfxReq));
		collectSql.append("		) \n");
		collectSql.append(") \n");
		collectSql.append(" group by tmp_age \n");
		collectSql.append(" order by tmp_age asc");
		log.info("查询年龄分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();
		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询年龄分布耗时: {}ms", end - start);
		return  list;
	}

	public List<ChartDataDetail> selectSexData(ZnfxReq znfxReq) {
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"    	   		sex as name, \n" +
				"				count(distinct username) as value \n" +
				" 		   from ( \n" +
				"      			select \n" +
			"						recharge.username,\n" +
				"        			IF(LENGTH(realname.id_num) = 18, if(toInt32OrZero(substring(realname.id_num, 17, 1 ))% 2 = 1, '男', '女'), '未实名') AS sex \n" +
				" 				from " + yunYingProperties.getDeviceregisterRechargetable() + "  recharge \n" +
				" 				left join " + yunYingProperties.getWanUserRealnameView() + " realname \n" +
				" 				on toUInt64(recharge.game_main) = realname.pgid \n" +
				" 				and realname.ai = recharge.username \n" +
				" 				and realname.status =0 \n" +
				" 				where  1=1 \n");
		collectSql.append(this.getRechargeParentchlFilterSql(znfxReq));
		collectSql.append(this.getPaydayFilterSql(znfxReq));
		collectSql.append("		and recharge.d_kid in \n");
		collectSql.append(" 	( \n");
		collectSql.append("    		select \n" +
				"						kid \n" +
				"					from " + yunYingProperties.getNinetydeviceregtable() + " reg \n" +
				"    				where 1=1 \n");
		collectSql.append(this.getRegParentchlFilterSql(znfxReq));
		collectSql.append(this.getRegAmountFilterSql(znfxReq));
		collectSql.append("		) \n");
		collectSql.append(") \n");
		collectSql.append(" group by sex \n");
		collectSql.append(" order by sex asc");
		log.info("查询性别分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();
		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询性别分布耗时: {}ms", end - start);
		return  list;
	}


	private String getPaydayFilterSql(ZnfxReq znfxReq){
		String startTime = znfxReq.getStartTime();
		String endTime = znfxReq.getEndTime();
		StringBuilder collectSql = new StringBuilder();
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			collectSql.append(" and recharge.pay_day>= " + startTime + " \n");
			collectSql.append(" and recharge.pay_day<=" + endTime + " \n");
		}
		return collectSql.toString();
	}


	private String getRechargeParentchlFilterSql(ZnfxReq znfxReq){
		String parentchlArr = znfxReq.getParentchlArr();
		StringBuilder collectSql = new StringBuilder();
		if(StringUtils.isNotBlank(parentchlArr)){
			if (parentchlArr.contains(",")) {
				collectSql.append(" and recharge.d_chl_main in ('" + parentchlArr.replaceAll(",", "','") + "') \n");
			} else {
				collectSql.append(" and recharge.d_chl_main  = '" + parentchlArr + "' \n");
			}
		}
		return collectSql.toString();
	}

	private String getRegAmountFilterSql(ZnfxReq znfxReq){
		String startAmount = znfxReq.getTotalPayCostMin();
		String endAmount = znfxReq.getTotalPayCostMax();
		StringBuilder collectSql = new StringBuilder();
		if(StringUtils.isNotBlank(startAmount)){
			collectSql.append(" and (reg.fee_total+reg.givemoney_total+reg.currency_amount_total)>= "+ startAmount +" \n");
		}
		if(StringUtils.isNotBlank(endAmount)){
			collectSql.append(" and (reg.fee_total+reg.givemoney_total+reg.currency_amount_total)<= "+ endAmount +" \n");
		}
		return collectSql.toString();
	}





	private String getRegParentchlFilterSql(ZnfxReq znfxReq){
		String parentchlArr = znfxReq.getParentchlArr();
		StringBuilder collectSql = new StringBuilder();
		if(StringUtils.isNotBlank(parentchlArr)){
			if (parentchlArr.contains(",")) {
				collectSql.append(" and reg.chl_main in ('" + parentchlArr.replaceAll(",", "','") + "') \n");
			} else {
				collectSql.append(" and reg.chl_main  = '" + parentchlArr + "' \n");
			}
		}
		return collectSql.toString();
	}
}